from util.singelMode import singleton
import os
import sqlite3
import sys
import util.ctime
@singleton
class CDb:
    def __init__(self):
        self.__db={}
        self.mkdir("chaindata/chainstate")
        self.mkdir("chaindata/index")
    def __call__(self):
        return self
    
    @property
    def db(self):
        
        return self.__db
    
    @db.setter
    def db(self,value):
        self.__db = value
    
    def init(self):
        pass
    
    def getValueByKey(self,dbname,key):
        #return self.__db[dbname].Get(bytes(key,'utf-8')).decode('utf-8')
        value = self.__db[dbname].get(bytes(key,'utf-8'))
        if value:
            return value.decode('utf-8')
        else:
            return value
    
    def setValue(self,dbname,key,value):
        #self.__db[dbname].Put(bytes(key,'utf-8') , bytes(value,'utf-8'))
        self.__db[dbname].put(bytes(key,'utf-8') , bytes(value,'utf-8'))
    
    
    def deleteValue(self,dbname,key):
        #self.__db[dbname].Delete(bytes(key,'utf-8'))
        self.__db[dbname].delete(bytes(key,'utf-8'))
    
    def adddb(self,name,db):
        self.__db[name]=db
    @staticmethod
    def mkdir(path):
    
    
        # 去除首位空格
        path=path.strip()
        # 去除尾部 \ 符号
        path=path.rstrip("\\")
    
        # 判断路径是否存在
        # 存在     True
        # 不存在   False
        isExists=os.path.exists(path)
    
        # 判断结果
        if not isExists:
            # 如果不存在则创建目录
            # 创建目录操作函数
            os.makedirs(path) 
    
            # print(path+' 创建成功')
            return True
        else:
            # 如果目录存在则不创建，并提示目录已存在
            # print(path+' 目录已存在')
            return False
@singleton
class Sqlite3Db:
    def __init__(self):
        self.mkdir('sqlitedb')
        self.dbname='./sqlitedb/info.db'
        self.initDb(self.dbname)
        pass
    def __call__(self):
        return self
    def initDb(self,dbname):
        sqlstrList=["CREATE TABLE IF NOT EXISTS node(hostAndport VARCHAR(25) PRIMARY KEY,state INT NOT NULL,time REAL NOT NULL)",
                    "CREATE TABLE IF NOT EXISTS user(telphone VARCHAR(25) PRIMARY KEY NOT NULL,username VARCHAR(20) NOT NULL,password VARCHAR(20) NOT NULL)",
                    "CREATE TABLE IF NOT EXISTS card(address VARCHAR(40) PRIMARY KEY NOT NULL,publickey TEXT NOT NULL,privatekey TEXT NOT NULL,telphone VARCHAR(20) NOT NULL,time REAL NOT NULL,FOREIGN KEY(telphone) REFERENCES user(telphone) ON DELETE CASCADE)"]
        conn = sqlite3.connect(dbname)
        c = conn.cursor()
        try:
            for sqlstr in sqlstrList:
                c.execute(sqlstr)
                conn.commit()
        except Exception as e:
            conn.close()
            print(e)
            sys.exit(2)
        else:
            conn.close()
       
           
    def executeSql(self,dbname,sqlstr,data=""):
        conn = sqlite3.connect(dbname)
        c = conn.cursor()
        try:
            if data=="":
                c.execute(sqlstr)
            else:
                if type(data)==list:
                    c.executemany(sqlstr,data)
                else:
                    c.execute(sqlstr,data)
            conn.commit()
        except Exception as e:
            print(e)
            return False
        else:
            conn.close()
            return True
    def select(self,dbname,sqlstr,data=""):
        conn = sqlite3.connect(dbname)
        c = conn.cursor()
        result=None
        try:
            if data=="":
                cursor = c.execute(sqlstr)
            else:
                if type(data)==list:
                    cursor=c.executemany(sqlstr,data)
                else:
                    cursor =c.execute(sqlstr,data)
        
        except Exception as e:
            print(e)
            result=None
            return result
        else:
            result=cursor.fetchall()
            conn.close()
            return result
    @staticmethod
    def mkdir(path):
        # 去除首位空格
        path=path.strip()
        # 去除尾部 \ 符号
        path=path.rstrip("\\")
    
        # 判断路径是否存在
        # 存在     True
        # 不存在   False
        isExists=os.path.exists(path)
    
        # 判断结果
        if not isExists:
            # 如果不存在则创建目录
            # 创建目录操作函数
            os.makedirs(path) 
    
            # print(path+' 创建成功')
            return True
        else:
            # 如果目录存在则不创建，并提示目录已存在
            # print(path+' 目录已存在')
            return False
        
    #==========================================================user table operate  begin=============================================
    def user_add(self,telphone,username,password):
        data=(telphone,username,password)
        sqlstr="INSERT INTO user VALUES(?,?,?)"
        return self.executeSql(self.dbname,sqlstr,data)
    def user_findByTel(self,telphone):
        data=(telphone,)
        sqlstr="SELECT *from user where telphone=?"
        return self.select(self.dbname,sqlstr,data)
    
    def user_findByTelAndPwd(self,tel,pwd):
        data=(tel,pwd)
        sqlstr="SELECT *from user where telphone=? and password=?"
        return self.select(self.dbname,sqlstr,data)
    #==========================================================user table operate  end =============================================

    #==========================================================card table operate  begin=============================================
    def card_add(self,address,publickey,privatekey,telphone):
        time=util.ctime.Ctime.getNowTimestamp()
        data=(address,publickey,privatekey,telphone,time)
        sqlstr="INSERT INTO card VALUES(?,?,?,?,?)"
        return self.executeSql(self.dbname,sqlstr,data)
    def card_findByAddress(self,address):
        data=(address,)
        sqlstr="SELECT *from card where address=?"
        return self.select(self.dbname,sqlstr,data)
    def card_findByTel(self,telphone):
        data=(telphone,)
        sqlstr="SELECT *from card where telphone=?"
        return self.select(self.dbname,sqlstr,data)
    def card_findByAddressAndTel(self,address,tel):
        data=(address,tel)
        sqlstr="SELECT *from card where address=? and telphone=?"
        return self.select(self.dbname,sqlstr,data)
    #==========================================================card table operate  end =============================================
    
    #==========================================================node table operate  begin=============================================
    def node_add(self,hostAndport,state=0):
        time=util.ctime.Ctime.getNowTimestamp()
        # hostAndport=f'{host}:{port}'
        data=(hostAndport,state,time)
        sqlstr="REPLACE INTO node VALUES(?,?,?)"
        return self.executeSql(self.dbname,sqlstr,data)
    def node_addNodes(self,nodes):
        if len(nodes)>0:
            data=[]
            time=util.ctime.Ctime.getNowTimestamp()
            for node in nodes:
                a=(node['hostAndport'],node['state'],time)
                data.append(a)
            sqlstr="REPLACE INTO node VALUES(?,?,?)"
            return self.executeSql(self.dbname,sqlstr,data)

    def node_updateStateByhostAndport(self,hostAndport,state):
        time=util.ctime.Ctime.getNowTimestamp()
        # hostAndport=f'{host}:{port}'
        data=(state,time,hostAndport)
        sqlstr="UPDATE node set state = ? ,time=? where hostAndport=?"
        return self.executeSql(self.dbname,sqlstr,data)

    def node_findByState(self,state=0,excludeNode=""):
        if excludeNode=="":
            data=(state,)
            sqlstr="SELECT hostAndport from node where state=?"
            return self.select(self.dbname,sqlstr,data)
        else:
            data=(state,excludeNode)
            sqlstr="SELECT hostAndport from node where state=? and hostAndport!=?"
            return self.select(self.dbname,sqlstr,data)

    def node_randomFindByState(self,state=0,n=1,excludeNode=""):
        if excludeNode=="":
            data=(state,n)
            sqlstr="SELECT hostAndport from node where state=?  ORDER BY RANDOM() limit ?"
            return self.select(self.dbname,sqlstr,data)
        else:
            data=(state,excludeNode,n)
            sqlstr="SELECT hostAndport from node where state=? and hostAndport!=?  ORDER BY RANDOM() limit ?"
            return self.select(self.dbname,sqlstr,data)

    def node_delByhostAndport(self,hostAndport):
        data=(hostAndport,)
        sqlstr="DELETE from node where hostAndport=?"
        return self.executeSql(self.dbname,sqlstr,data)
   
   






    #==========================================================node table operate  end =============================================

